package com.sharemarking.wa.platform.mapper;

import com.sharemarking.wa.platform.dto.cms.CmsPostListDto;
import com.sharemarking.wa.platform.dto.index.*;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author dxy
 */
@Repository
public interface IndexMapper {
    @Select("SELECT id,cover AS pic,title,publish_time FROM cms_post WHERE hidden = 0 Order by publish_time DESC LIMIT 3")
    List<NewsListDto> getNewsList(Long time);

    @Select("SELECT id AS channelId,display_name AS channelName,link AS pic FROM cms_channel WHERE parent = 1 ORDER BY sort LIMIT 5")
    List<ChannelListDto> getChannelList();

    @Select("SELECT b.id AS orgId,a.name AS orgName FROM wa_agreement a LEFT JOIN sys_org b ON b.real_name = a.name \n" +
            "WHERE a.exam_state = 1  AND a.org_id = (SELECT org_id FROM sys_org_member WHERE member_id = #{memberId}) AND b.id IS NOT NULL AND a.expire_date > NOW() LIMIT 4")
    List<OrgListDto> getOrgList(Integer memberId);

    @Select("SELECT distinct waste_type_name FROM wa_org_waste_type WHERE org_id = #{orgId}")
    List<String> getOrgWasteByOrgId(Integer orgId);

    @Select("SELECT a.org_id,a.name,a.license_number,concat(province,city,area,address)AS address,b.nick_name AS contact,b.mobile FROM wa_org_verify a LEFT JOIN sys_member b ON a.member_id = b.id WHERE a.org_id = #{orgId}")
    OrgInfoDto getOrgInfo(Integer orgId);

    @Select("SELECT waste_type_id as wasteId,waste_type_name AS wasteType FROM wa_org_waste_type WHERE org_id = #{orgId} GROUP BY wasteId")
    List<OrgWasteListDto> getOrgWasteListByOrgId(Integer orgId);

    @Select("SELECT id AS articleId,cover AS pic,title,publish_time FROM cms_post WHERE title like CONCAT('%',#{search},'%') ")
    List<CmsPostListDto> searchPostByContent(String search);

    @Select("SELECT b.name AS orgName,b.org_id AS orgId FROM wa_org_waste_type a LEFT JOIN wa_org_verify b ON a.org_verify_id = b.id\n" +
            "WHERE (b.name like CONCAT('%',#{search},'%') OR a.waste_type_name like CONCAT('%',#{search},'%') ) AND b.exam_state = 1 \n" +
            "GROUP BY b.name")
    List<OrgListDto> searchOrgByName(String search);

    @Select("SELECT cover AS pic FROM cms_post WHERE channel_id = 13")
    List<BannerListDto> getBannerList();

    @Select("SELECT COUNT(*) FROM sys_message WHERE member_id = #{memberId} and is_push_sms =0 ")
    Integer getMessageNumber(Integer memberId);
}
